﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace Accumulation.Database.SqlDbHelper {
    /// <summary>
    /// SQL 语句执行
    /// <remarks>
    /// MatchZhou - 2009/1/20
    /// </remarks>
    /// </summary>
    public class SqlDbText : DbBase {

        #region 不带参数

        /// <summary>
        /// 执行 sql 语句,返回受影向的行数
        /// </summary>
        /// <param name="sqlString">sql语句</param>
        /// <returns>受影向的行数</returns>
        public static int ExecuteNonQuery(string sqlString) {
            return SqlDbText.ExecuteNonQuery(sqlString, null);
        }
        /// <summary>
        /// 执行一条计算查询结果语句,返回查询结果
        /// </summary>
        /// <param name="sqlString">sql语句</param>
        /// <returns>查询结果</returns>
        public static object ExecuteScalar(string sqlString) {
            return SqlDbText.ExecuteScalar(sqlString, null);
        }
        /// <summary>
        /// 执行查询语句，返回 DataReader，使用后手动释放资源
        /// </summary>
        /// <param name="sqlString">sql语句</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string sqlString) {
            return SqlDbText.ExecuteReader(sqlString, null);
        }
        /// <summary>
        /// 执行查询语句,返回 DataTable
        /// </summary>
        /// <param name="sqlString">sql语句</param>
        /// <returns>DataTable</returns>
        public static DataTable QueryDataTable(string sqlString) {            
            return SqlDbText.QueryDataSet(sqlString).Tables[0];
        }
        /// <summary>
        /// 执行查询语句,返回 DataSet
        /// </summary>
        /// <param name="sqlString">sql语句</param>
        /// <returns>DataSet</returns>
        public static DataSet QueryDataSet(string sqlString) {
            return SqlDbText.QueryDataSet(sqlString, null);
        }

        #endregion

        #region 带参执行

        /// <summary>
        /// 执行 sql 语句,返回受影向的行数
        /// </summary>
        /// <param name="sqlString">sql 语句</param>
        /// <param name="sqlParams">sql 参数组</param>
        /// <returns>返回受影向的行数</returns>
        public static int ExecuteNonQuery(string sqlString, params SqlParameter[] sqlParams) {
            using (SqlConnection conn = new SqlConnection(DbBase.ConnectionString)) {
                using (SqlCommand cmd = new SqlCommand(sqlString, conn)) {
                    try {
                        conn.Open();
                        DbBase.PrepareCommand(cmd, sqlParams);
                        int rows = cmd.ExecuteNonQuery();
                        return rows;
                    }
                    catch (SqlException e) {
                        conn.Close();
                        throw e;
                    }
                }
            }
        }
        /// <summary>
        /// 执行一条计算查询结果语句,返回查询结果
        /// </summary>
        /// <param name="sqlString">sql 语句</param>
        /// <param name="sqlParams">sql 参数</param>
        /// <returns>查询结果</returns>
        public static object ExecuteScalar(string sqlString, params SqlParameter[] sqlParams) {
            using (SqlConnection conn = new SqlConnection(DbBase.ConnectionString)) {
                using (SqlCommand cmd = new SqlCommand(sqlString, conn)) {
                    try {
                        conn.Open();
                        DbBase.PrepareCommand(cmd, sqlParams);
                        object obj = cmd.ExecuteScalar();
                        if (Object.Equals(obj, null) || Object.Equals(obj, DBNull.Value)) {
                            return null;
                        }
                        else {
                            return obj;
                        }
                    }
                    catch (SqlException e) {
                        conn.Close();
                        throw e;
                    }
                }
            }
        }
        /// <summary>
        /// 执行查询语句,返回 SqlDataReader
        /// 使用后手动关闭 SqlDataReader 释放资源
        /// </summary>
        /// <param name="sqlString">sql语句</param>
        /// <param name="sqlParams">sql参数</param>
        /// <returns>DataReader</returns>
        public static SqlDataReader ExecuteReader(string sqlString, params SqlParameter[] sqlParams) {
            SqlConnection conn = new SqlConnection(DbBase.ConnectionString);
            SqlCommand cmd = new SqlCommand(sqlString, conn);
            try {
                conn.Open();
                DbBase.PrepareCommand(cmd, sqlParams);
                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return dr;
            }
            catch (SqlException e) {
                if (conn.State != ConnectionState.Closed) conn.Close();
                throw e;
            }
        }
        /// <summary>
        /// 执行查询语句,返回 DataTable
        /// </summary>
        /// <param name="sqlString">sql 语句</param>
        /// <param name="sqlParams">sql 参数</param>
        /// <returns>DataTable</returns>
        public static DataTable QueryDataTable(string sqlString, params SqlParameter[] sqlParams) {
            return SqlDbText.QueryDataSet(sqlString, sqlParams).Tables[0];
        }
        /// <summary>
        /// 执行查询语句,返回 DataTable
        /// </summary>
        /// <param name="sqlString">sql 语句</param>
        /// <param name="sqlParams">sql 参数</param>
        /// <returns>DataSet</returns>
        public static DataSet QueryDataSet(string sqlString, params SqlParameter[] sqlParams) {
            using (SqlConnection conn = new SqlConnection(DbBase.ConnectionString)) {
                using (SqlCommand cmd = new SqlCommand(sqlString, conn)) {
                    DataSet ds = new DataSet();
                    try {
                        conn.Open();
                        DbBase.PrepareCommand(cmd, sqlParams);
                        SqlDataAdapter da = new SqlDataAdapter(cmd);
                        da.Fill(ds);
                    }
                    catch (SqlException e) {
                        throw e;
                    }
                    finally {
                        conn.Close();
                    }
                    return ds;
                }
            }
        }

        #endregion

    }
}